<?php

class FPostgresCatalogue extends FCatalogue
{
    
    /**
     * Método para gerar um id para um novo registro na tabela $table.
     * @author Bruno Edgar Fuhr [brunofuhr@yahoo.com.br]
     * @since 29/03/2011
     * 
     * @param $table - Tabela em que se deseja criar o novo id.
     * @param $field - Campo que é chave primária da tabela. Default 'codigo'.
     */
    public function getIdForTable($table, $field = 'codigo')
    {
        $result = null;
        
        if ( $table )
        {
            $sql = new FSQL();
            $sql->addTable($table);
            $sql->addColumn("MAX($field) AS index");
            
            $result = $this->db->query($sql->select());
        }

        $index = $result[0]['index'] ? $result[0]['index'] + 1 : 1;

        return $index;
    }
    
    /**
     * Listar os nomes das colunas de uma tabela.
     * TODO: considerar esquema.
     * 
     * @param string $table Nome da tabela.
     * @return array Vetor com a listagem de colunas da tabela
     */
    public function getTableColumns($table)
    {
        $sql = new FSQL();        
        $sql->addTable('pg_catalog.pg_attribute', 'a');
        $sql->addColumn('a.attname');
        $sql->addInnerJoin('pg_stat_user_tables c', 'a.attrelid = c.relid');
        $sql->setWhere('a.attnum > ?', '0');
        $sql->setWhereAnd('NOT a.attisdropped');
        $sql->setWhereAnd("c.relname = '?'", strtolower($table));
        //$sql->setOrderBy('c.relname, a.attname');
        
        $colunas = $this->db->query($sql->select());

        $coluna = array();

        foreach ( $colunas as $info )
        {
            $coluna[] = $info['attname'];
        }

        return $coluna;
    }

    /**
     * Listar as informações de uma coluna da tabela.
     * 
     * @param String $table
     * @param String $column
     * @return Array
     */
    public function getColumnInfo($table, $column)
    {
        $columnInfo = new FColumnInfo();
        
        if ( $table && $column )
        {
            $sql = new FSQL();
            
            $sql->addColumn('a.attname AS name');
            $sql->addColumn('t.typname as type');
            $sql->addColumn('t.typoutput as typoutput');
            $sql->addColumn('a.atttypmod-4 as length');
            $sql->addColumn('pg_catalog.format_type(a.atttypid, a.atttypmod)');
            $sql->addColumn('a.attnotnull as not_null');
            $sql->addColumn('substring(d.adsrc for 128) as default');
            $sql->addColumn('coalesce((SELECT i.indisprimary AS primary_key
                FROM pg_class bc,
                    pg_index i,
                    pg_attribute ia
                WHERE (bc.oid = i.indrelid)
                    AND (ia.attrelid = i.indexrelid) 
                    AND (bc.relname = c.relname)
                    AND ia.attname = a.attname
                    AND i.indisprimary = true
                    LIMIT 1),false) as primary_key');
            $sql->addColumn('coalesce((SELECT i.indisunique AS unique_key
                FROM pg_class bc,
                     pg_index i,
                     pg_attribute ia
               WHERE (bc.oid = i.indrelid)
                    AND (ia.attrelid = i.indexrelid) 
                    AND (bc.relname = c.relname)
                    AND ia.attname = a.attname
                    AND i.indisunique = true
                    LIMIT 1),false) as unique_key');
            $sql->addColumn('col_description(c.oid, a.attnum) AS comment');
            
            $sql->addTable('pg_class', 'c');
            
            $sql->addLeftJoin('pg_attribute a', 'a.attrelid = c.oid');
            $sql->addLeftJoin('pg_type t', 'a.atttypid = t.oid');
            $sql->addLeftJoin('pg_catalog.pg_attrdef d', 'd.adrelid = a.attrelid AND d.adnum = a.attnum');
            $sql->addLeftJoin('pg_catalog.pg_tables ta', 'ta.tablename = c.relname');

            $sql->setWhere('a.attnum > 0');
            $sql->setWhere('NOT a.attisdropped');
            $sql->setWhere('c.relname = ?', "'$table'");
            $sql->setWhere('schemaname = ?', "'public'");
            $sql->setWhere('a.attname = ?', "'$column'");
            
            $info = $this->db->query($sql->select());

            $columnInfo->setName($info[0]['name']);
            
            if ( substr_count($info[0]['typoutput'], 'varchar') > 0 )
            {
                $columnInfo->setType('varchar');
            }
            elseif ( substr_count($info[0]['typoutput'], 'bpchar') > 0 )
            {
                $columnInfo->setType('bpchar');
            }
            elseif ( substr_count($info[0]['typoutput'], 'char') > 0 )
            {
                $columnInfo->setType('char');
            }
            elseif ( substr_count($info[0]['typoutput'], 'int8') > 0 )
            {
                $columnInfo->setType('int8');
            }
            elseif ( substr_count($info[0]['typoutput'], 'int') > 0 )
            {
                $columnInfo->setType('int4');
            }
            elseif ( substr_count($info[0]['typoutput'], 'float') > 0 )
            {
                $columnInfo->setType('float');
            }
            elseif ( substr_count($info[0]['typoutput'], 'numeric') > 0 )
            {
                $columnInfo->setType('numeric');
            }
            elseif ( substr_count($info[0]['typoutput'], 'double') > 0 )
            {
                $columnInfo->setType('double');
            }
            elseif ( substr_count($info[0]['typoutput'], 'date') > 0 )
            {
                $columnInfo->setType('date');
            }
            elseif ( substr_count($info[0]['typoutput'], 'time') > 0 )
            {
                $columnInfo->setType('timestamp');
            }
            elseif ( substr_count($info[0]['typoutput'], 'text') > 0 )
            {
                $columnInfo->setType('text');
            }
            elseif ( substr_count($info[0]['typoutput'], 'bool') > 0 )
            {
                $columnInfo->setType('boolean');
            }
            else
            {
                $columnInfo->setType($info[0]['type']);
            }
            
            $columnInfo->setLength($info[0]['length']);
            $columnInfo->setFormatType($info[0]['format_type']);
            $columnInfo->setNotNull($info[0]['not_null']);
            $columnInfo->setDefault($info[0]['default']);
            $columnInfo->setPrimaryKey($info[0]['primary_key']);
            $columnInfo->setUniqueKey($info[0]['unique_key']);
            $columnInfo->setComment($info[0]['comment']);
        }
        
        return $columnInfo;
    }

    /**
     * Obtém as chaves primárias da tabela.
     * 
     * @param String $table - Nome da tabela
     * @return Array com os nomes e tipos das chaves primárias.
     */
    public function getTablePrimaryKey($table)
    {
        $sql = new FSQL();
        
        $sql->addColumn('A.attname AS name');
        $sql->addColumn('format_type(T.oid, null) AS type');
        
        $sql->addTable('pg_class', 'C');
        $sql->addInnerJoin('pg_attribute A', 'C.oid = A.attrelid');
        $sql->addInnerJoin('pg_index I', 'C.oid = I.indrelid');
        $sql->addInnerJoin('pg_type T', 'A.atttypid = T.oid');

        // Encontra até 5 chaves primárias, se for o caso :o
        $sql->setWhere('(I.indkey[0] = A.attnum OR I.indkey[1] = A.attnum OR I.indkey[2] = A.attnum OR I.indkey[3] = A.attnum OR I.indkey[4] = A.attnum)');
        $sql->setWhere("I.indisprimary = '?'", 't');
        $sql->setWhere("C.relname = '?'", $table);
        $keys = $this->db->query($sql->select());
        
        return $keys;
    }
    
    public function getTableForeignKeys($table)
    {
        $sql = new FSQL();
        
        $sql->addColumn('a.attname AS attribute');
        $sql->addColumn('clf.relname AS table_ref');
        $sql->addColumn('af.attname AS attribute_ref');
        
        $sql->addTable('pg_catalog.pg_attribute a');
        $sql->addInnerJoin('pg_catalog.pg_class cl', "a.attrelid = cl.oid AND cl.relkind = 'r'");
        $sql->addInnerJoin('pg_catalog.pg_namespace n', "n.oid = cl.relnamespace");
        $sql->addInnerJoin('pg_catalog.pg_constraint ct', "a.attrelid = ct.conrelid AND ct.confrelid != 0 AND ct.conkey[1] = a.attnum");
        $sql->addInnerJoin('pg_catalog.pg_class clf', "ct.confrelid = clf.oid AND clf.relkind = 'r'");
        $sql->addInnerJoin('pg_catalog.pg_namespace nf', "nf.oid = clf.relnamespace");
        $sql->addInnerJoin('pg_catalog.pg_attribute af', "af.attrelid = ct.confrelid AND af.attnum = ct.confkey[1]");
        
        $sql->setWhere("cl.relname = '?'", $table);
        
        $keys = $this->db->query($sql->select());
        
        return $keys;
    }
    
    /**
     * Função que obtém as tabelas que referenciam esta como chave estrangeira.
     */
    public function getForeignTables($table)
    {
        $sql = new FSQL();
        
        $sql->addColumn('conname AS fk_name');
        $sql->addColumn('conrelid::pg_catalog.regclass AS table');
        $sql->addColumn('pg_catalog.pg_get_constraintdef(c.oid, true) AS condef');
        
        $sql->addTable('pg_catalog.pg_constraint c');
        $sql->addInnerJoin('pg_catalog.pg_class r', 'r.oid = c.confrelid');
        $sql->addInnerJoin('pg_catalog.pg_namespace n', 'n.oid = r.relnamespace');
        
        $sql->setWhere("r.relname = '?'", $table);
        $sql->setWhere("c.contype = 'f'");
        
        $sql->setOrderBy('fk_name');
        
        return $this->db->query($sql->select());
    }
}


?>
